const express = require('express');
const router = express.Router();
const utils = require("../utils/index.js");
const {primary} = require("../utils/roleString");
const pools = require("../utils/pools.js");
const xlsx = require('node-xlsx');


//获取员工销售额数据
router.post('/employeeSale', async (req, res) => {
    try {
        let {startDate,endDate,vehicleModel} = req.body;


        // 构建SQL查询语句
        let sql = `SELECT * FROM salesreport where 1=1 `;

        // 添加日期范围条件
        if (startDate && endDate) {
            sql += ` AND sale_begin_data BETWEEN ? AND ? `;
        }

        // 添加车型条件
        if (vehicleModel) {
            sql += ` AND sale_type =  ` + '"'+vehicleModel+'" ';
        }

        sql += `order by sale_begin_data`;


        // 执行数据库查询
        await pools({sql,val:[startDate,endDate,vehicleModel],run:false,res,req});


    } catch (error) {
        // 返回错误响应
        res.json({
            code: 500,
            msg: '获取员工销售额数据失败',
            error: error.message
        });
    }
});

//获取销售量数据
router.post('/saleNum', async (req, res) => {
    try {

        // 构建SQL查询语句
        let sql = `SELECT * FROM overallsales where 1=1`;


        // 执行数据库查询
        await pools({sql,val:[],run:false,res,req});


    } catch (error) {
        // 返回错误响应
        res.json({
            code: 500,
            msg: '获取销售量数据失败',
            error: error.message
        });
    }
});

//获取库存报表数据
router.post('/stock', async (req, res) => {
    try {

        // 构建SQL查询语句
        let sql = `SELECT * FROM vehicleinventory`;


        // 执行数据库查询
        await pools({sql,val:[],run:false,res,req});


    } catch (error) {
        // 返回错误响应
        res.json({
            code: 500,
            msg: '获取库存报表数据失败',
            error: error.message
        });
    }
});


//导出数据
router.post("/exportSaleNumData",async (req,res)=>{
    let sql = 'SELECT total_sales,sales_amount,growth_rate,report_date FROM overallsales';
    let {result} = await pools({sql,res,req});
    console.log(result)
    let data =[ ['销售总量','销售额','销售增长率','报表日期']];
    result.map(t=>{
        data.push(Object.values(t))
    })
    let buffer = xlsx.build([{name:'sheet1',data}]);
    const filename = '整体销售量报表数.xlsx';
    const encodedFilename = encodeURI(filename);
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;filename='+encodedFilename);
    res.send(buffer);
});

module.exports = router;